package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;

import com.canin.model.Bed;
import com.canin.util.CaninUtil;

public class BedDAO
{
    private Connection conn = null;

    private Map<String, String> sizes = CaninUtil.initializeSizes();

    public BedDAO(Connection conn)
    {
        this.conn = conn;
    }

    public ArrayList<Bed> getAllBed() throws Exception
    {
        ArrayList<Bed> bedList = new ArrayList<Bed>();

        String sql = "select * from bed";
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Bed bed = new Bed();
            bed.setId( rs.getInt( "id" ) );
            bed.setName( rs.getString( "name" ) );
            bed.setBrand( rs.getString( "brand" ) );
            bed.setDescription( rs.getString( "description" ) );
            bed.setSize( rs.getString( "size" ) );
            bed.setWidht( rs.getDouble( "width" ) );
            bed.setLength( rs.getDouble( "length" ) );
            bed.setHeight( rs.getDouble( "height" ) );
            bed.setShippingPrice( rs.getInt( "shippingPrice" ) );
            bed.setPrice( rs.getInt( "price" ) );
            
        	bed.setThaiSize( sizes.get( bed.getSize() ) );
            
            bedList.add( bed );
        }

        rs.close();
        ps.close();

        return bedList;
    }
	
	public TreeMap<String, String> getBedBrand() throws Exception
	{
		TreeMap<String, String> bedBrandList = new TreeMap<String, String>();
		
		String sql = "select distinct(brand) from bed where brand!=''";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			bedBrandList.put( rs.getString( "brand" ), rs.getString( "brand" ) );
		}

		rs.close();
		ps.close();
		
		return bedBrandList;
	}
	
	public void addNewBed( Bed bed ) throws Exception
	{
		String sql = "insert into bed(name, brand, description, size, price, shippingPrice, width, length, height)" +
				" value(?,?,?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, bed.getName());
		ps.setString(2, bed.getBrand());
		ps.setString(3, bed.getDescription());
		ps.setString(4, bed.getSize());
		ps.setInt(5, bed.getPrice());
		ps.setInt(6, bed.getShippingPrice());
		ps.setDouble(7, bed.getWidht());
		ps.setDouble(8, bed.getLength());
		ps.setDouble(9, bed.getHeight());
		
		ps.executeUpdate();
		ps.close();
	}

    public ArrayList<Bed> getBedBySize( String size ) throws Exception
    {
        ArrayList<Bed> bedList = new ArrayList<Bed>();

        String sql = "select * from bed where size=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, size);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Bed bed = new Bed();
            bed.setId( rs.getInt( "id" ) );
            bed.setName( rs.getString( "name" ) );
            bed.setBrand( rs.getString( "brand" ) );
            bed.setDescription( rs.getString( "description" ) );
            bed.setSize( rs.getString( "size" ) );
            bed.setWidht( rs.getDouble( "width" ) );
            bed.setLength( rs.getDouble( "length" ) );
            bed.setHeight( rs.getDouble( "height" ) );
            bed.setShippingPrice( rs.getInt( "shippingPrice" ) );
            bed.setPrice( rs.getInt( "price" ) );
            bed.setImageName( rs.getString( "imageName" ) );
            
        	bed.setThaiSize( sizes.get( bed.getSize() ) );
            
            bedList.add( bed );
        }

        rs.close();
        ps.close();

        return bedList;
    }

    
}
